'''
----登录系统(MySQL版）---
使用MySQL保存数据，实现以下业务。

一、建立以下数据表并填充数据
用户表：users
列名：主键id	用户名username 密码password

二、实现以下菜单并实现业务:
1.登录；
2.注册；
3.实现MVC三层设计，M(Model)、V(View)、C(Control)

'''
import pymysql

host = 'localhost'
port = 3306
user = 'root'
password = 'root'
database = 'dbtest'
charset = 'utf8'

# 创建数据库的连接对象，连接数据库
db = pymysql.Connect(host=host, port=port, user=user, password=password, database=database, charset=charset)
print('数据库已连接')

# 创建游标对象
cursor = db.cursor()

# 创建表
sql = """
create table users (id int primary key auto_increment, username varchar(20), password varchar(10) )
"""
try:
    cursor.execute(sql)
    db.commit()
    print("表创建成功")
except Exception as e:
    db.rollback()
    print(e)

# 注册
def register():
    while True:
        user = input("请输入想要注册的用户名：")
        pwd1 = input("请输入密码：")
        pwd2 = input("请再次输入密码：")
        if pwd1 == pwd2:
            sql = f""" 
            insert into users values(null,'{user}','{pwd2}')
            """
            try:
                cursor.execute(sql)
                db.commit()  # 事务提交
                print('注册成功')
            except Exception as e:
                db.rollback()  # 事务回滚
                print(e)
            break
        else:
            print("两次密码不一致，请重新输入")
            continue

def login():
    count = 0
    while True:
        tag = 0
        user = input('请输入用户名：')
        pwd = input('请输入密码：')
        sql = f"""
            select * from users where username = {user} and password = {pwd}
        """
        try:
            cursor.execute(sql)
            print('登陆成功')
            tag = 1
        except Exception as e:
            print(e)
        if tag == 0:
            count += 1
            if count == 3:
                print("用户已被锁定")
                break
            else:
                print(f"用户名或密码错误，请重新输入！还剩{3 - count}次登录机会")
                continue
        else:
            break
    return tag

def print_menu():
    print('----欢迎----')
    print('1.注册')
    print('2.登录')
    print('3.退出')

def main():
    # r = login()
    # if r == 1:
    while True:
        print_menu()
        choice = int(input('请输入你的选择：'))
        if choice == 1:
            register()
        elif choice == 2:
            login()
        elif choice == 3:
            print('退出成功')
            break
        else:
            print('输入有误，请重新输入')

if __name__ == '__main__':
    main()